from typing import Union

from sqlalchemy import update, CursorResult, text, func
from sqlmodel import select, desc, col, asc
from sqlmodel.sql._expression_select_cls import SelectOfScalar, Select

from src.base.beans import PageResult
from src.config.db import mysqlClient
from src.domain.sys_tenant import SysTenant, SysTenantReq


def get_tenant_by_id(tenant_id: int) -> SysTenant:
    return mysqlClient.exec(
        select(SysTenant).where(SysTenant.tenant_id == tenant_id).where(SysTenant.del_flag == False)).first()


def where_handle(stmt: Union[Select, SelectOfScalar], entity: SysTenant = None):
    stmt.where(SysTenant.del_flag == False)
    if entity:
        if entity.tenant_id:
            stmt = stmt.where(SysTenant.tenant_id == entity.tenant_id)
        if entity.tenant_name:
            stmt = stmt.where(SysTenant.tenant_name == entity.tenant_name)
        if entity.status:
            stmt = stmt.where(SysTenant.status == entity.status)
    return stmt


def get_tenant_list_by_condition(entity: SysTenant = None) -> list[SysTenant]:
    stmt = select(SysTenant)
    stmt = where_handle(stmt, entity)
    # order_by() 是返回一个新的语句对象，而不是原地修改
    stmt = stmt.order_by(desc(SysTenant.sort))
    users = mysqlClient.exec(stmt)
    return users


def get_tenant_page_by_condition(entity: SysTenantReq = None) -> PageResult:
    search = SysTenant(**entity.model_dump())
    list_stmt = select(SysTenant)
    list_stmt = where_handle(list_stmt, search)
    if entity.sorter:
        list_stmt = list_stmt.order_by(text(entity.sorter))
    else:
        list_stmt = list_stmt.order_by(asc(SysTenant.sort))
    list_stmt = list_stmt.limit(entity.size).offset(entity.size * (entity.current - 1))
    users = mysqlClient.exec(list_stmt).all()

    total_stmt = select(func.count(SysTenant.user_id))
    total_stmt = where_handle(total_stmt, search)
    total_result = mysqlClient.exec(total_stmt)
    total = total_result.one_or_none()
    return PageResult(total=total, data=users, current=entity.current, size=entity.size)


def insert_tenant(entity: SysTenant = None):
    mysqlClient.add(entity)


def update_tenant(entity: SysTenant = None) -> int:
    stmt = update(SysTenant).values(entity.model_dump()).where(col(SysTenant.tenant_id).is_(entity.tenant_id))
    result: CursorResult = mysqlClient.exec(stmt)
    mysqlClient.commit()
    return result.rowcount()


def delete_tenant(tenant_id: int = None) -> int:
    stmt = update(SysTenant).values(SysTenant(del_flag=True).model_dump()).where(
        col(SysTenant.tenant_id).is_(tenant_id))
    result: CursorResult = mysqlClient.exec(stmt)
    mysqlClient.commit()
    return result.rowcount()


def delete_tenants(tenant_ids: list[int] = None) -> int:
    stmt = update(SysTenant).values(SysTenant(del_flag=True).model_dump()).where(
        col(SysTenant.tenant_id).in_(tenant_ids))
    result: CursorResult = mysqlClient.exec(stmt)
    mysqlClient.commit()
    return result.rowcount()
